USE [BMIG_Mensual_POST_CADENA]
GO

IF EXISTS (SELECT name FROM sysobjects WHERE name = N'[dbo].[WASP_M0014_SEGURO_CESANTIA_BASE]' AND type = 'P')
  DROP PROCEDURE [dbo].[WASP_M0014_SEGURO_CESANTIA_BASE]
GO


CREATE PROCEDURE [dbo].[WASP_M0014_SEGURO_CESANTIA_BASE]
(
	@FECHA_INICIO  CHAR(8),
	@FECHA_FIN  CHAR(8)
)
AS

BEGIN 
		SELECT 	
				jt73109FAP,
				null as FECHAS,
				null as APELLIDO1,
				null as APELLIDO2,
				null as NOMBRE1,
				null as NOMBRE2,     
				null as RUT,
				null as DV,
				null as COMUNATRAB,
				null as FECNAC,	
				REPLACE(CONVERT(VARCHAR,CAST(JT73109FAP AS DATETIME),105),'-','') AS  FECHAOTCRE,
				null as FACREDITO,
				null as MONCRE,
				null AS FOLIO,
				null AS NUMCRED,
				a.ppcta, 
				a.ppsuc, 
				a.ppoper
			
		INTO   dbo.as_seg_cesantia_1

		FROM 
			fsd611 a(nolock),
			fsd601 b(nolock),
			JT73109 coloc(nolock)
			
		WHERE
				(a.ppimp11 = 105 or
				a.ppimp12 = 105 or
				a.ppimp13 = 105 or
				a.ppimp14 = 105 or
				a.ppimp15 = 105 or
				a.ppimp16 = 105 or
				a.ppimp17 = 105 or
				a.ppimp18 = 105 or
				a.ppimp19 = 105 ) AND
				a.pgcod = b.pgcod and
				a.ppmod = b.ppmod and
				a.ppsuc = b.ppsuc and 
				a.ppmda = b.ppmda and
				a.ppcta = b.ppcta and 
				a.ppoper = b.ppoper and
				a.ppsbop = b.ppsbop and
				a.pptope = b.pptope and
				A.ppoper = coloc.JT73109FPA and
				A.ppsuc = coloc.JT73109SUC and
				a.pptipo = '' and a.ppexte = 9 and
				coloc.jt73109FAP between @FECHA_INICIO  and @FECHA_FIN and
				b.d601co = 'S' 
END

BEGIN
		SELECT
				distinct
				a.jt73109FAP,
				a.FECHAS,
				rtrim(ltrim(per.pfape1)) as APELLIDO1,
       			rtrim(ltrim(per.pfape2)) as APELLIDO2,
       			rtrim(ltrim(per.pfnom1)) as NOMBRE1,
				rtrim(ltrim(per.pfnom2)) as NOMBRE2,
				rel.ctnro as RUT,
				rtrim(ltrim(substring(per .Pfndoc,len(per.Pfndoc),1))) as [DV],
				dir.docallp + convert(varchar,dir.donrop)as DIRTRABAJA, 
				NULL as COMUNATRAB,
				replace(Convert(varchar,per.Pffnac,105),'-','') AS FECNAC,
				FECHAOTCRE,
				replace(Convert(varchar,b.AOFVAL,105),'-','') AS FACREDITO,
				b.aoimp as MONCRE,
				b.aosuc,
       			b.aooper AS FOLIO,
				b.aooper AS NUMCRED,
				b.aocta,
				b.aopap,
				b.aomda,
				b.aopre,
				rel.Pendoc
			
		INTO   dbo.seg_cesantia_2
		
		FROM
				dbo.as_seg_cesantia_1 as a (nolock),
				fsd010 b (nolock),
				fsd005 as dir (nolock)
				LEFT JOIN fsr008 rel(nolock)ON rel.Pendoc = dir.Pendoc ,
				fsd002 per (nolock),
				fsd008 tp (nolock)
				
		WHERE
				a.ppcta = b.aocta and
       			a.ppsuc = b.aosuc and
       			a.ppoper= b.aooper and
				a.ppcta = rel.Ctnro and
				rel.Pendoc = per.Pfndoc and
				rel.Ctnro = tp.Ctnro and 
--				rel.Pendoc *= dir.Pendoc and
				tp.Ctccli in(3,5) and 
				b.aosbop = 0 and
				NOT EXISTS(SELECT *
        					   FROM dbo.as_desistidos as anu
        					   WHERE a.ppcta = anu.hcta and
	              					 a.ppoper = anu.hoper and
	       							a.ppsuc = anu.hsucur)
END


--
BEGIN
		SELECT 
			 DISTINCT	jt73109FAP,
						FECHAS,
						APELLIDO1,
						APELLIDO2,
						NOMBRE1,
						NOMBRE2,
						RUT,
						DV,
						DIRTRABAJA,
						COM.COMUNATRAB as COMUNATRAB,
						FECNAC,
						FECHAOTCRE,
						FACREDITO,
						MONCRE,
						aosuc,
						FOLIO,
						NUMCRED,
						aocta,
						aopap,
						aomda,
						aopre

		INTO	dbo.seg_cesantia 
		FROM
				dbo.seg_cesantia_2 AS A (nolock)
				LEFT JOIN comunas  COM (nolock) ON Pendoc = COM.Z07326NDO
		--WHERE
				--Pendoc *= COM.Z07326NDO
END